package com.tskj.leonsu.db;


import com.alibaba.fastjson.JSONObject;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @notes:
 * @program: ss
 * @author: JRX
 * @create: 2019-07-19 13:58
 **/

/**
 * 调用Apache Commons DBUtil组件的数据库操作类
 * 采用DBCP作为数据源，数据源在Spring中已经配置好
 * 本类已经在Spring中配置好，在需要的地方，set注入后即可调用,或者@Autowired注解注入
 * private DbUtilitys dbUtilsTemplate;
 * public void setDbUtilsTemplate(DbUtilitys dbUtilsTemplate) {
 * this.dbUtilsTemplate = dbUtilsTemplate;
 * }
 */
@Component
public class DbUtilitys_gygl {
    private String driverClassName;
    /*注解方式*/
    //@Autowired
    //private DruidDataSource dataSource;
    @Autowired
    private QueryRunner queryRunner_gygl;
    private byte DBType;

    /*public DbUtilitys(QueryRunner queryRunner) {
        this.queryRunner = queryRunner;
    }

    public DbUtilitys() {

    }*/

    private static final Log LOG = LogFactory.getLog(DbUtilitys_gygl.class);

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    //获取数据库类型
    public byte getDBType() {
        return -1;
    }
    // set注入方式
    //public void setDataSource(DruidDataSource dataSource) {
    //    this.dataSource = dataSource;
    //}

    public List<Map<String, Object>> execSQL(String sql, Object... params) {
        List<Map<String, Object>> list;
        //queryRunner = new QueryRunner(dataSource);
        try {
            if (params == null) {
                list = (List<Map<String, Object>>) queryRunner_gygl.query(sql, new MapListHandler());
            } else {
                list = (List<Map<String, Object>>) queryRunner_gygl.query(sql, new MapListHandler(), params);
            }
        } catch (Exception e) {
            e.printStackTrace();
            LOG.error("Error occured while attempting to query data", e);
            //如果想让spring事务回滚必须在catch中加入异常抛出否则@Transactional注解不能管理事务进行回滚
            System.out.println(sql);
            throw new RuntimeException("查询出错");
        }
        return list;
    }

    public List<Map<String, Object>> execCallSQL(String sql, String tablename, String keyFieldName, String find, String power, int limit, int page, String sort, String groupName) {

        try {
            DataSource dataSource = queryRunner_gygl.getDataSource();
            Connection conn = dataSource.getConnection();
            //Statement statement = conn.createStatement();


            CallableStatement cstmt = conn.prepareCall(sql);
            cstmt.setString(1, tablename);
            cstmt.setString(2, keyFieldName);
            cstmt.setInt(3, limit);
            cstmt.setInt(4, page);
            cstmt.setString(5, find);
            cstmt.setString(6, sort);
            cstmt.setString(7, power);
            cstmt.setString(8, groupName);

            boolean execute = cstmt.execute();


        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    public List<Map<String, Object>> execSQL(String sql, Object params) {
        List<Map<String, Object>> list;
        //queryRunner = new QueryRunner(dataSource);
        //System.err.println(dataSource);
        try {
            if (params == null) {
                list = (List<Map<String, Object>>) queryRunner_gygl.query(sql, new MapListHandler());
            } else {
                list = (List<Map<String, Object>>) queryRunner_gygl.query(sql, new MapListHandler(), params);
            }
        } catch (Exception e) {
            e.printStackTrace();
            LOG.error("Error occured while attempting to query data", e);
            //System.err.println("sql報錯");
            //如果想让spring事务回滚必须在catch中加入异常抛出否则@Transactional注解不能管理事务进行回滚
            throw new RuntimeException("查询出错");
        }
        return list;
    }

    public List<Map<String, Object>> execSQL(String sql) {
        List<Map<String, Object>> list;
        //queryRunner = new QueryRunner(dataSource);
        //System.err.println(dataSource);
        try {
            list = (List<Map<String, Object>>) queryRunner_gygl.query(sql, new MapListHandler());
        } catch (Exception e) {
            e.printStackTrace();
            LOG.error("Error occured while attempting to query data", e);
            //System.err.println("sql報錯");
            //如果想让spring事务回滚必须在catch中加入异常抛出否则@Transactional注解不能管理事务进行回滚
            throw new RuntimeException("查询出错");
        }
        return list;
    }

    public int execSQLWithTrans(String sql) throws Exception {
        return execSQLWithTrans(sql, (Object[]) null);
    }

    public int execSQLWithTran(String sql, Object... params) throws Exception {
        return execSQLWithTrans(sql, params);
    }

    //普通删除
    public int execSQLWithTrans(String sql, Object[] params) throws Exception {

        //queryRunner = new QueryRunner(dataSource);
        //System.err.println(dataSource);
        int affectedRows;
        try {
            if (params == null) {
                affectedRows = queryRunner_gygl.update(sql);
            } else {
                affectedRows = queryRunner_gygl.update(sql, params);
            }
            //System.err.println("修改的条数:" + affectedRows);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("SQL语句发生错误", new Throwable("SQL语句发生错误"));
        }
        return affectedRows;
    }

    /**
     * 执行Update、Delete等SQL语句，带参数，使用事务 执行成功，返回0，否则返回1
     * <code>需要注意的是QueryRunner预编译如果传值为null不会和以前一样保存空" "</code>
     */
    public int execSQLWithTrans(String sql, List<String> params,
                                List<Map<String, Object>> values) throws Exception {
        Object[][] objects = ListToArray(values, params);
        //if (objects.length > 0) {
        return queryRunner_gygl.batch(sql, objects).length;
        //System.err.println("修改条数=" + affectedRows + "条?");
                /*if (affectedRows > 0) {
                    return 0;
                } else {
                    return 1;
                }*/
        //} else {
        //    throw new RuntimeException("空参数。如果不需要参数，则传递一个空数组,事务回滚...");
        //    //return 1;
        //}
    }

    /**
     * @return Object[][]
     * @Title: ListToArray
     * @Description: list列表转换成二维数组
     * @Author: Administrator
     * @param: @param list
     * @param: @param KeyLenght每行的列数，按最长的计算
     * @param: @return
     */
    private static Object[][] ListToArray(List<Map<String, Object>> list, List<String> params) {
        if (CollectionUtils.isEmpty(list) || CollectionUtils.isEmpty(params)) {
            return new Object[0][];
        }
        Object[][] array = new Object[list.size()][params.size()];
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            for (int j = 0; j < params.size(); j++) {
                //System.err.println(map.get(params.get(j)));
                if (map.get(params.get(j)) == null) {
                    array[i][j] = "";
                } else {
                    array[i][j] = map.get(params.get(j));
                }
            }
            //System.err.println(Arrays.toString(array[i]));
        }
        return array;
    }


    //根据ID批量删除
    public int execSQLWithTrans(String sql, List<String> params) throws Exception {
        //queryRunner = new QueryRunner(dataSource);
        Object[] array = new Object[params.size()];
        params.toArray(array);
        Object[][] values = new Object[array.length][1];
        for (int i = 0; i < array.length; i++) {
            values[i][0] = array[i];
        }
        return queryRunner_gygl.batch(sql, values).length;
    }

    //批量删除
    public int execWithTrans(String sql, List<String> params) {
        //queryRunner = new QueryRunner(dataSource);
        Object[] array = new Object[params.size()];
        params.toArray(array);
        Object[][] values = new Object[array.length][1];
        for (int i = 0; i < array.length; i++) {
            values[i][0] = array[i];
        }
        try {
            int affectedRows = queryRunner_gygl.batch(sql, values).length;
            /*if (affectedRows > 0) {
                return 0;
            } else {
                return 1;
            }*/
        } catch (Exception e) {
            e.printStackTrace();
            //如果想让spring事务回滚必须在catch中加入异常抛出否则@Transactional注解不能管理事务进行回滚
            throw new RuntimeException("SQL语句发生错误", new Throwable("SQL语句发生错误"));
            //return 1;
        }
        return 0;
    }
    //该方法不知道如何改 有用到
   /* public ResultSetMetaData getResultSetMetaData(String sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.executeQuery();
            return ps.getMetaData();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                ps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }*/

    /**
     * 查询出结果集中的第一条记录，并封装成Map对象
     *
     * @param sql sql语句
     * @return 封装为Map的对象
     */
    public Map<String, Object> findFirst(String sql) {
        return findFirst(sql, null);
    }

    /**
     * 查询出结果集中的第一条记录，并封装成Map对象
     *
     * @param sql   sql语句
     * @param param 参数
     * @return 封装为Map的对象
     */
    public Map<String, Object> findFirst(String sql, Object param) {
        return findFirst(sql, new Object[]{param});
    }

    /**
     * 查询出结果集中的第一条记录，并封装成Map对象
     *
     * @param sql    sql语句
     * @param params 参数数组
     * @return 封装为Map的对象
     */
    @SuppressWarnings("unchecked")
    public Map<String, Object> findFirst(String sql, Object[] params) {
        //queryRunner = new QueryRunner(dataSource);
        Map<String, Object> map = null;
        try {
            if (params == null) {
                map = (Map<String, Object>) queryRunner_gygl.query(sql, new MapHandler());
            } else {
                map = (Map<String, Object>) queryRunner_gygl.query(sql, new MapHandler(), params);
            }
        } catch (Exception e) {
            e.printStackTrace();
            LOG.error("Error occured while attempting to query data", e);
            //如果想让spring事务回滚必须在catch中加入异常抛出否则@Transactional注解不能管理事务进行回滚
            throw new RuntimeException("SQL语句发生错误", new Throwable("SQL语句发生错误"));
        }
        return map;
    }

    public JSONObject execSQL(String sql, List<String> params, List<Map<String, Object>> values) throws Exception {
        JSONObject ret = JSONObject.parseObject("{}");
        if (execSQLWithTrans(sql, params, values) == 0) {
            ret.put("result", 0);
        } else {
            ret.put("result", 1);
            ret.put("errmsg", "SQL执行失败");
        }
        return ret;
    }

    public static void main(String[] args) {
        Map<String, Object> map = new HashMap<>();
        Map<String, Object> map2 = new HashMap<>();
        /**
         * @Author JRX
         * <code>CollectionUtils.isEmpty(li)判断了 为null和""空对象两种情况</code>
         **/
        /*List<Map<String, Object>> li = new ArrayList<>();
        //List<Map<String, Object>> li = null;
        System.err.println(CollectionUtils.isEmpty(li));*/
        List<Map<String, Object>> list = new ArrayList<>();
        map.put("a", null);
        map.put("b", "b2");
        map.put("e", "e5");
        map.put("f", "f6");
        map.put("c", "c3");
        map.put("d", "d4");
        list.add(map);
        map2.put("a", "a5");
        map2.put("g", "g9");
        map2.put("b", "b6");
        map2.put("h", "h10");
        map2.put("c", "c7");
        //map2.put("d", "d8");

        list.add(map2);
        System.err.println(list);
        List<String> ls = new ArrayList<>();
        ls.add("a");
        ls.add("b");
        ls.add("c");
        ls.add("d");
        //Object[][] objects = new DbUtilitys().ListToArray(list, ls);
        Object[][] objects = ListToArray(list, ls);
        System.err.println(objects.length);
        /*int size = list.size();
        Object[][] array = new Object[size][ls.size()];
        for (int i = 0; i < size; i++) {//循环遍历所有行
            array[i] = list.get(i).values().toArray();//每行的列数
        }
        System.err.println(array.toString());*/
    }

    /**
     * 修改数据
     *
     * @param sql
     * @param params
     * @return
     * @throws Exception
     */
    public int update(String sql, Object[] params) throws Exception {
        int row = queryRunner_gygl.update(sql, params);
        return row;
    }


}